SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[BackTestingPivot] 
 @FundId   VARCHAR(MAX ),

@StartDate DATETIME ,
@EndDate DATETIME  	
 AS
BEGIN
	SET FMTONLY ON;
    DECLARE @fmtonlyON BIT
    SET @fmtonlyON = 0
    IF ( 1 = 0 ) 
        BEGIN
            SET @fmtonlyON = 1
        END
    SET FMTONLY OFF;

	
    

DECLARE @ExportId INT  
-- SET @FundId   = 'FSIC'
 SET @ExportId =
 (SELECT MAX(e.ExportId)
 FROM    FSFundModel.Reporting.Export e
 WHERE   e.FundId = @FundId
 )
 SELECT  * FROM (
 SELECT DISTINCT t.tradeid 'PivotID', i.ExtId 'LXID', i.InstrName, actsum.ActivityDate  TradeDateDate, ptable.Mid, ptable.PricingId--,NAVDAtES.NAVDate , 

 , CASE WHEN NAVDAtES.NAVDate > actsum.ActivityDate THEN  'Trade Date +'   +  CAST( ABS((RANK() OVER (PARTITION BY t.TradeId  ORDER BY NAVDAtES.NAVDate ASC))-6) AS VARCHAR(50))
 WHEN NAVDAtES.NAVDate < actsum.ActivityDate THEN   'Trade Date -'   +  CAST( ABS((RANK() OVER (PARTITION BY t.TradeId  ORDER BY NAVDAtES.NAVDate ASC))-6) AS VARCHAR(50))
 ELSE 'Trade Date' END AS dateFcn
	

 FROM        FSFundModel.Reporting.Trade t
            
            INNER JOIN FSFundModel.Reporting.Asset a
            ON         a.ExportId     = t.ExportId
            AND        t.InstrumentId = a.InstrumentId
            AND        t.LotId        = a.LotId
			AND t.PortfolioId = a.PortfolioId

			INNER JOIN  FSFundModel.Reporting.ActivitySummary actsum
			ON t.ExportId = actsum.ExportId
			AND t.ActivitySummaryId = actsum.ActivitySummaryId


            
            INNER JOIN FSFundModel.Reporting.Instrument i
            ON         i.ExportId     = t.ExportId
            AND        t.InstrumentId = i.InstrumentId
            
            
            INNER JOIN FSFundModel.Reporting.InstrumentDefinition id
            ON         id.ExportId = t.ExportId
            AND        i.InstDefId = id.InstDefId
            
            
            INNER JOIN FSFundModel.Reporting.Issuer iss
            ON         iss.ExportId = t.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel.Reporting.Portfolio p
            ON         t.PortfolioId = p.PortfolioId
            AND        p.ExportId    = t.ExportId
            
            INNER JOIN FSFundModel.Reporting.Fund f
            ON         f.ExportId = t.ExportId
            AND        f.FundId   = @FundId
 
 CROSS APPLY (SELECT  prc.NAVDate	 FROM ReportingScripts.dbo.tvfNAVDatesByFund(actsum.ActivityDate, 5,1,1) AS prc) NAVDAtES
 outer APPLY (SELECT  p.PricingId ,
         p.PriceDate ,
         p.Bid ,
         p.Offer ,
         p.Mid ,
         p.PricingSource 
        
         FROM FSFundModel.dbo.Pricing p WHERE p.PriceDate = NAVDAtES.NAVDate AND p.PricingId = i.ExtId) AS ptable

WHERE      t.ExportId         = @ExportId
 AND        actsum.ActivityType     = 'Sale'
 AND     actsum.ActivityDate BETWEEN @StartDate AND     @EndDate
 

 ) x 


 PIVOT 
 (max(x.Mid) FOR dateFcn IN (
[Trade Date -5]	,
[Trade Date -4]	,
[Trade Date -3]	,
[Trade Date -2]	,
[Trade Date -1]	,
[Trade Date]	,
[Trade Date +1]	,
[Trade Date +2]	,
[Trade Date +3]	,
[Trade Date +4]	,
[Trade Date +5]	))  t2
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Accounting', 'SCHEMA', N'dbo', 'PROCEDURE', N'BackTestingPivot', NULL, NULL
GO
